class Drug < ActiveRecord::Base
  has_and_belongs_to_many :categories, :order => 'name, id ASC'
  has_many :ratings
  include SideffectHelper
  def self.top(count)
    sql = <<-SQL
      SELECT d.*, t.cnt2 cnt
      FROM drugs d,
      (
        SELECT r.drug_id id2, count(distinct r.id) cnt2
        FROM ratings r
        GROUP BY r.drug_id
        ORDER BY cnt2 DESC
        LIMIT ?
      ) t
      WHERE d.id = t.id2
      ORDER BY cnt DESC, d.name ASC
    SQL
    find_by_sql([sql, count])
  end
  
  def self.with_prefix(prefix)
    find(:all, :conditions => ['name like ?', prefix + '%'], :order => 'name ASC')
  end
  
  def average_score(filter_options = {})
    ratings.average(:score, :conditions => [filter_sql(filter_options.merge(:ratings_table_name => 'ratings'))]).to_f
  end
  
  def ratings_number(filter_options = {})
    ratings.count(:conditions => [filter_sql(filter_options.merge(:ratings_table_name => 'ratings'))])
  end
  
  def ranking(category, filter_options = {})
    sql = <<-SQL
      SELECT count(*)
      FROM
      (
        SELECT r.drug_id
        FROM ratings r, drugs d, categories_drugs cd
        WHERE cd.category_id = ?
        AND #{filter_sql(filter_options)}
        GROUP BY r.drug_id
        HAVING avg(r.score) > (SELECT avg(r2.score) FROM ratings r2 WHERE r2.drug_id = ? )
      ) t
    SQL
    self.class.count_by_sql([sql, category.id, self[:id]]) + 1
  end
  
  def sideffects(filter_options = {})
    sql = <<-SQL
      SELECT s.*, count(distinct r.id) cnt
      FROM sideffects s, ratings r, ratings_sideffects rs
      WHERE r.drug_id = ?
      AND r.id = rs.rating_id 
      AND s.id = rs.sideffect_id
      AND #{filter_sql(filter_options)}
      GROUP BY s.id 
      ORDER BY cnt DESC, s.name ASC
    SQL
    Sideffect.find_by_sql([sql, self[:id]])
  end
  
  def sex_stats(filter_options = {})
    {
      'Male'    => ratings.count(:conditions => [filter_sql(filter_options.merge(:ratings_table_name => 'ratings', :sex => 'M'))]),
      'Female'  => ratings.count(:conditions => [filter_sql(filter_options.merge(:ratings_table_name => 'ratings', :sex => 'F'))]),
      'Unknown' => ratings.count(:conditions => [filter_sql(filter_options.merge(:ratings_table_name => 'ratings', :sex => 'U'))])
    }
  end
  
  def age_stats(filter_options = {})
    {
      '0 - 18'    => ratings.count(:conditions => [filter_sql(filter_options.merge(:ratings_table_name => 'ratings', :age => '18'))]),
      '19 - 45'   => ratings.count(:conditions => [filter_sql(filter_options.merge(:ratings_table_name => 'ratings', :age => '45'))]),
      '46 - 60'   => ratings.count(:conditions => [filter_sql(filter_options.merge(:ratings_table_name => 'ratings', :age => '60'))]),
      '60 +'      => ratings.count(:conditions => [filter_sql(filter_options.merge(:ratings_table_name => 'ratings', :age => 'inf'))]),
      'Unknown'   => ratings.count(:conditions => [filter_sql(filter_options.merge(:ratings_table_name => 'ratings', :age => 'U'))])
    }
  end
  
  def time_taken_stats(filter_options = {})
    {
      '0 day - 1 week'     => ratings.count(:conditions => [filter_sql(filter_options.merge(:ratings_table_name => 'ratings', :time_taken => 'week'))]),
      '1 week - 1 month'   => ratings.count(:conditions => [filter_sql(filter_options.merge(:ratings_table_name => 'ratings', :time_taken => 'month'))]),
      '1 month - 3 months' => ratings.count(:conditions => [filter_sql(filter_options.merge(:ratings_table_name => 'ratings', :time_taken => 'season'))]),
      '3 months - 6 months' => ratings.count(:conditions => [filter_sql(filter_options.merge(:ratings_table_name => 'ratings', :time_taken => 'half_year'))]),
      '6 months - 1 year'  => ratings.count(:conditions => [filter_sql(filter_options.merge(:ratings_table_name => 'ratings', :time_taken => 'year'))]),
      '1 year +'           => ratings.count(:conditions => [filter_sql(filter_options.merge(:ratings_table_name => 'ratings', :time_taken => 'inf'))]),
      'Unknown'            => ratings.count(:conditions => [filter_sql(filter_options.merge(:ratings_table_name => 'ratings', :time_taken => 'U'))])
    }
  end
end
